package com.dopios.services.persistance.dao;

import com.dopios.services.persistance.domain.CityAdvice;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;


public class AdviceImplDao implements AdviceDao {
    public static final String INSERT_CITY_ADVICE =
            "INSERT INTO CityAdvices (CityId, UserAddedId,Advice, DateCreated, DateEdited) VALUES ( (Select CityId from Cities where CityName=?), (Select UserId from users where Username=?),?,?,null)";
    public static final String GET_ADVICE_BY_CITY_USER =
            "SELECT * FROM CityAdvice WHERE CityId = (Select CityId from cities where CityName=?) AND UserId=(Select UserId from users where Username=?)";
    public static final String CHECK_USER_EXISTS =
            "SELECT UserId FROM Users where Username=?";
    public static final String CHECK_CITY_EXISTS =
            "SELECT CityId FROM Cities where CityName=?";
    public static final String INSERT_CITY_ADVICE_TAGS =
            "REPLACE INTO adviceTags (TagId,AdviceId) VALUES ";
    public static final String SELECT_CITY_ADVICES_FOR_CITY = "SELECT CityAdvices.*,CityName,Rating,usercityadvicerating.userRatedId as ratedBy,usercityadvicerating.adviceRatingId FROM CityAdvices " +
            "LEFT JOIN usercityadvicerating ON usercityadvicerating.cityAdviceId=cityadvices.Id LEFT OUTER JOIN cities on cities.CityId=cityadvices.CityId Where cityName=?  ORDER BY rating DESC ";
    public static final String SELECT_CITY_ADVICES_FOR_CITY_USER = "SELECT CityAdvices.*,CityName,Rating,usercityadvicerating.userRatedId as userRatedBy,usercityadvicerating.adviceRatingId FROM CityAdvices " +
            "LEFT JOIN usercityadvicerating ON usercityadvicerating.cityAdviceId=cityadvices.Id LEFT OUTER JOIN cities on cities.CityId=cityadvices.CityId Where cityName=?  AND userAddedId!=?  AND cityadvices.UserAddedId " +
            "NOT IN  (SELECT userVisibilityExclusions.ExcludedUserId FROM userVisibilityExclusions WHERE userVisibilityExclusions.userId=?) ORDER BY rating DESC";
    public static final String UPDATE_OR_INSERT_USER_ADVICE_RATINGS = "REPLACE INTO usercityadvicerating (usercityadvicerating.cityAdviceId,usercityadvicerating.userRatedId, usercityadvicerating.rating ) VALUES ";
    public static final String SELECT_TAGS_KEYWORDS = "SELECT tag,keyword,Tags.tagId FROM Tags,tagkeywords where tags.tagId=tagkeywords.TagId";
    private JdbcTemplate jdbcTemplate;
    private static final Logger logger = Logger.getLogger(AdviceImplDao.class);
    public static final String INSERT_USER_VISIBILITY_EXCLUSION =
            "REPLACE INTO userVisibilityExclusions (UserId, ExcludedUserId) VALUES (?,?)";

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public int addCityAdvice(final CityAdvice advice) throws DataAccessException {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new org.springframework.jdbc.core.PreparedStatementCreator() {
            java.sql.Date postedDate = new java.sql.Date(advice.getDateCreated().getTime());
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps =
                        connection.prepareStatement(INSERT_CITY_ADVICE, new String[]{"id"});
                ps.setString(1, advice.getCityName());
                ps.setString(2, advice.getUserAddedId());
                ps.setString(3, advice.getAdvice());
                ps.setDate(4, postedDate);
                return ps;
            }
        },
                keyHolder);
        logger.debug("Added advice:" + advice.getAdvice() + " for city:" + advice.getCityName() + " and user:" + advice.getUserAddedId());
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<CityAdvice> getListCityAdvicesByCityUser(String user, String city) {
        logger.debug("Query to getListCityAdvicesByCityUser:"+SELECT_CITY_ADVICES_FOR_CITY_USER);
        List<CityAdvice> cityAdvices = jdbcTemplate.query(SELECT_CITY_ADVICES_FOR_CITY_USER,
                new BeanPropertyRowMapper(CityAdvice.class), city,user,user);
        return cityAdvices;
    }

    @Override
    public List<CityAdvice> getListCityAdvicesByCity(String city) {
        logger.debug("Query to getListCityAdvicesByCity:"+SELECT_CITY_ADVICES_FOR_CITY);
        List<CityAdvice> cityAdvices = jdbcTemplate.query(SELECT_CITY_ADVICES_FOR_CITY,
                new BeanPropertyRowMapper(CityAdvice.class), city);
        return cityAdvices;
    }

    @Override
    public void addTagsForCityAdvice(int adviceId, List<Map<String,Object>> matchedTags) throws DataAccessException {
        String query = INSERT_CITY_ADVICE_TAGS;
        for(Map tagRow:matchedTags){
            Integer tagId =  (Integer)tagRow.get("tagId");
            query = query + "(" + tagId + "," + adviceId+"),";
               }
               query=query.substring(0, query.length()-1);
               int result = jdbcTemplate.update(query);
               logger.debug("Query to addTagsForCityAdvice:"+query);
    }

    @Override
    public boolean checkUserExists(String user) {
        List results = jdbcTemplate.queryForList(CHECK_USER_EXISTS, user);
        return results != null && results.size() > 0;
    }

    @Override
    public boolean checkCityExists(String city) {
        List results = jdbcTemplate.queryForList(CHECK_CITY_EXISTS, city);
        return results != null && results.size() > 0;
    }

    @Override
    public int getUserIdByUsername(String user) {
        Integer userId = jdbcTemplate.queryForInt(CHECK_USER_EXISTS, user);
        return userId;
    }

    @Override
    public void updateAddUserAdvicesRatings(List<CityAdvice> cityAdvices, int userRatedId) {
        String query = UPDATE_OR_INSERT_USER_ADVICE_RATINGS;
        for (CityAdvice cityAdvice : cityAdvices) {
            if (cityAdvice.getRating() != null && !cityAdvice.getRating().equals("")) {
                query = query + "(" + cityAdvice.getId() + "," + userRatedId + "," + cityAdvice.getRating() + "),";
            }
        }
        query=query.substring(0, query.length()-1);
        int result = jdbcTemplate.update(query);
        logger.debug("Query to update/insert rating:"+query);
    }

    @Override
    public void updateUserVisibilityExclusion( int userId,int excludedUserId) {
        logger.debug("Query to updateUserVisibilityExclusion:"+INSERT_CITY_ADVICE_TAGS);
        int result = jdbcTemplate.update(INSERT_USER_VISIBILITY_EXCLUSION, userId,excludedUserId);
    }

    @Override
    public List<Map<String, Object>> getTagsKeywords() {
        List<Map<String, Object>> tagKeywords = jdbcTemplate.queryForList(SELECT_TAGS_KEYWORDS);
        return tagKeywords;
    }

}
